package nc.bs.impl.hopg;

import com.alibaba.fastjson.JSONObject;
import nc.bs.dao.BaseDAO;
import nc.bs.itf.hopg.IYhtToHOPGService;
import nc.jdbc.framework.processor.MapListProcessor;
import nc.vo.pub.BusinessException;
import nc.vo.pub.lang.UFDateTime;
import org.apache.commons.lang3.StringUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class YhtToHOPGServiceImpl implements IYhtToHOPGService {
    public BaseDAO baseDAO;

    @Override
    public List<Map<String, Object>> getHOPGYSDataByName(JSONObject json) throws BusinessException {
        List<Map<String,Object>> lists = (List<Map<String,Object>>)this.getBaseDAO().executeQuery(this.buildHOPGYSDateByNameSQL(json), new MapListProcessor());
        if(lists == null && lists.isEmpty() ) return new ArrayList<Map<String, Object>>();
        for (Map<String, Object> map : lists) {
            Object xmname = map.get("xmname");
            if (xmname == null) continue;
            List<Map<String,Object>> xms = (List<Map<String,Object>>)this.getBaseDAO().executeQuery(this.buildSupXMSQL(xmname), new MapListProcessor());
            if (xms != null && !xms.isEmpty()){
                for (Map<String, Object> xm : xms) {
                    Object jc = xm.get("jc");
                    if ((jc == null || "".equals(jc)) || "3".equals(jc)){
                        map.put("threeXMName", xm.get("pjname"));
                        map.put("threeXMCode", xm.get("billno"));
                    } else if ("1".equals(jc)) {
                        map.put("oneXMName", xm.get("pjname"));
                        map.put("onXMCode", xm.get("billno"));
                    }else if ("2".equals(jc)) {
                        map.put("twoXMName", xm.get("pjname"));
                        map.put("twoXMCode", xm.get("billno"));
                    }
                }
            }else{
                for (Map<String, Object> xm : xms) {
                    Object jc = xm.get("jc");
                    if ((jc == null || "".equals(jc)) || "3".equals(jc)){
                        map.put("threeXMName", xm.get("pjname"));
                        map.put("threeXMCode", xm.get("billno"));
                    } else if ("1".equals(jc)) {
                        map.put("oneXMName", xm.get("pjname"));
                        map.put("onXMCode", xm.get("billno"));
                    }else if ("2".equals(jc)) {
                        map.put("twoXMName", xm.get("pjname"));
                        map.put("twoXMCode", xm.get("billno"));
                    }

                }
            }
        }
        return lists != null && !lists.isEmpty() ? lists : new ArrayList<Map<String, Object>>();
    }

    @Override
    public List<Map<String, Object>> getHOPGYSDataByCode(JSONObject json) throws BusinessException {
        List<Map<String,Object>> lists = (List<Map<String,Object>>)this.getBaseDAO().executeQuery(this.buildHOPGYSDateByCodeSQL(json), new MapListProcessor());
        if(lists == null && lists.isEmpty() ) return new ArrayList<Map<String, Object>>();
        for (Map<String, Object> map : lists) {
            Object xmname = map.get("xmname");
            if (xmname == null) continue;
            List<Map<String,Object>> xms = (List<Map<String,Object>>)this.getBaseDAO().executeQuery(this.buildSupXMSQL(xmname), new MapListProcessor());
            if (xms != null && !xms.isEmpty()){
                for (Map<String, Object> xm : xms) {
                    Object jc = xm.get("jc");
                    if ((jc == null || "".equals(jc)) || "3".equals(jc)){
                        map.put("threeXMName", xm.get("pjname"));
                        map.put("threeXMCode", xm.get("billno"));
                    } else if ("1".equals(jc)) {
                        map.put("oneXMName", xm.get("pjname"));
                        map.put("onXMCode", xm.get("billno"));
                    }else if ("2".equals(jc)) {
                        map.put("twoXMName", xm.get("pjname"));
                        map.put("twoXMCode", xm.get("billno"));
                    }
                }
            }else{
                for (Map<String, Object> xm : xms) {
                    Object jc = xm.get("jc");
                    if ((jc == null || "".equals(jc)) || "3".equals(jc)){
                        map.put("threeXMName", xm.get("pjname"));
                        map.put("threeXMCode", xm.get("billno"));
                    } else if ("1".equals(jc)) {
                        map.put("oneXMName", xm.get("pjname"));
                        map.put("onXMCode", xm.get("billno"));
                    }else if ("2".equals(jc)) {
                        map.put("twoXMName", xm.get("pjname"));
                        map.put("twoXMCode", xm.get("billno"));
                    }

                }
            }
        }
        return lists;
    }

    /**
     * 构建三级项目查询SQL
     * @return
     * @throws BusinessException
     */
    public String buildSupXMSQL(Object xmname) throws BusinessException {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT  \n" );
        sql.append("   billno,pjname,def1 jc\n" );
        sql.append(" FROM hrppl_pjappl\n" );
        sql.append(" START WITH pjname = '"+xmname+"'\n" );
        sql.append(" CONNECT BY PRIOR belongpro = pk_pjappl");
        return  sql.toString();
    }

    /**
     * 构建通过维度编码查询预算监控分析SQL
     * @param json
     * @return
     * @throws BusinessException
     */
    private String buildHOPGYSDateByCodeSQL(JSONObject json) throws BusinessException {
        StringBuilder sql = new StringBuilder();
        sql.append(" \tSELECT \n" );
        sql.append("\t\toos.code orgcode, oos.name orgname,dept.code deptcode,dept.name deptname, \n" );
        sql.append("\t\tpjappl.billno xmcode,pjappl.pjname xmname,pjappl.def1 jc, pjappl.pjtotalcost xmzjf,bis.code szxmcode,bis.name szxmname,\n" );
        sql.append("\t\tbf.code zjlycode,bf.name zjlyname,\n" );
        sql.append("\t\thbc.nyear, budgettype.code ystype, budgettype.name ysname,\n" );
        sql.append("\t\tbmd.amount_firstdown pfs,\n" );
        sql.append("\t\thbd.controlamount ndyss,hbd.adjustamount tzs,hbd.executeamount zzs,hbd.preoccuamount zxs,\n" );
        sql.append("\t\t(nvl(hbd.controlamount,0) - nvl( hbd.executeamount, 0) - NVL(hbd.preoccuamount, 0)) kyys,\n" );
        sql.append("\t\thbd.freezeamount djs\n" );
        sql.append("\tFROM\n" );
        sql.append("\t\thrpbctp_busictrl hbc\n" );
        sql.append("\tleft join hrpbctp_busictrldata hbd ON hbc.pk_busictrl = hbd.pk_busictrl\n" );
        sql.append("\tleft join org_orgs oos on hbc.pk_org = oos.pk_org\n" );
        sql.append("\tleft join HRPBM_BUDGETDEPT dept on dept.pk_budgetdept = hbc.pk_budgetdept\n" );
        sql.append("\tleft join HRPBM_BUDGETTYPE budgettype on hbc.pk_budgettype = budgettype.pk_budgettype\n" );
        sql.append("\tleft join hrppl_pjappl pjappl on pjappl.pk_pjappl  = hbd.dim3\n" );
        sql.append("\tleft join bd_inoutbusiclass bis on bis.pk_inoutbusiclass = hbd.dim4\n" );
        sql.append("\tleft join bd_fundsource bf on bf.pk_fundsource = hbd.dim5\n" );
        sql.append("\tleft join hrpbm_budgetmakedetail bmd on bmd.pk_budgetmakedetail = hbd.busibillbid\n" );
        sql.append("\tWHERE 1 = 1\n" );
        sql.append("\t\tAND hbc.dr = 0 \n" );
        sql.append("\t\tAND hbd.dr = 0 \n" );
//        sql.append("\t\tAND hbd.pk_busictrl IN ( '1001A110000000AT8O1M' )\n" );
        String orgCame = "00101";// 默认组织是
        if(!"".equals(json.getString("orgCode")) &&  json.getString("orgCode") != null) {
            orgCame = json.getString("orgCode");
        }
        sql.append("\t\tand oos.code = '"+orgCame+"'\n" );
        String nyear = "";
        if("".equals(json.getString("nyear")) || json.getString("nyear") ==null){
            String[] date = new UFDateTime().toString().split("-");
            nyear = date[0];
        }else{
            nyear =  json.getString("nyear");
        }
        sql.append("\t\tand hbc.nyear = '"+nyear+"'\n" );

        if(!"".equals(json.getString("deptCode")) &&  json.getString("deptCode") != null) {
            sql.append("\t\tand dept.code = '"+json.getString("deptCode")+"' \n" );
        }
        if(!"".equals(json.getString("szxmCode")) &&  json.getString("szxmCode") != null) {
            sql.append("\t\tand bis.code = '"+json.getString("szxmCode")+"' \n" ); // -- 收支项目
        }
        if(!"".equals(json.getString("xmCode")) &&  json.getString("xmCode") != null) {
            sql.append("\t\tand pjappl.billno = '"+json.getString("xmCode")+"' \n" ); // -- 项目名称
        }
        if(!"".equals(json.getString("zjlyCode")) &&  json.getString("zjlyCode") != null) {
            sql.append("\t\tand bf.code = '"+json.getString("zjlyCode")+"'  \n"); // --- 资金来源名称
        }
        return sql.toString();
    }
    /**
     * 构建通过维度名称查询预算监控分析SQL
     * @param json
     * @return
     * @throws BusinessException
     */
    private String buildHOPGYSDateByNameSQL(JSONObject json) throws BusinessException {
        StringBuilder sql = new StringBuilder();
        sql.append(" \tSELECT \n" );
        sql.append("\t\toos.code orgcode, oos.name orgname,dept.code deptcode,dept.name deptname, \n" );
        sql.append("\t\tpjappl.billno xmcode,pjappl.pjname xmname,pjappl.def1 jc, pjappl.pjtotalcost xmzjf,bis.code szxmcode,bis.name szxmname,\n" );
        sql.append("\t\tbf.code zjlycode,bf.name zjlyname,\n" );
        sql.append("\t\thbc.nyear, budgettype.code ystype, budgettype.name ysname,\n" );
        sql.append("\t\tbmd.amount_firstdown pfs,\n" );
        sql.append("\t\thbd.controlamount ndyss,hbd.adjustamount tzs,hbd.executeamount zzs,hbd.preoccuamount zxs,\n" );
        sql.append("\t\t(nvl(hbd.controlamount,0) - nvl( hbd.executeamount, 0) - NVL(hbd.preoccuamount, 0)) kyys,\n" );
        sql.append("\t\thbd.freezeamount djs\n" );
        sql.append("\tFROM\n" );
        sql.append("\t\thrpbctp_busictrl hbc\n" );
        sql.append("\tleft join hrpbctp_busictrldata hbd ON hbc.pk_busictrl = hbd.pk_busictrl\n" );
        sql.append("\tleft join org_orgs oos on hbc.pk_org = oos.pk_org\n" );
        sql.append("\tleft join HRPBM_BUDGETDEPT dept on dept.pk_budgetdept = hbc.pk_budgetdept\n" );
        sql.append("\tleft join HRPBM_BUDGETTYPE budgettype on hbc.pk_budgettype = budgettype.pk_budgettype\n" );
        sql.append("\tleft join hrppl_pjappl pjappl on pjappl.pk_pjappl  = hbd.dim3\n" );
        sql.append("\tleft join bd_inoutbusiclass bis on bis.pk_inoutbusiclass = hbd.dim4\n" );
        sql.append("\tleft join bd_fundsource bf on bf.pk_fundsource = hbd.dim5\n" );
        sql.append("\tleft join hrpbm_budgetmakedetail bmd on bmd.pk_budgetmakedetail = hbd.busibillbid\n" );
        sql.append("\tWHERE 1 = 1\n" );
        sql.append("\t\tAND hbc.dr = 0 \n" );
        sql.append("\t\tAND hbd.dr = 0 \n" );
//        sql.append("\t\tAND hbd.pk_busictrl IN ( '1001A110000000AT8O1M' )\n" );
        String orgName = "厦门大学附属心血管病医院";// 默认组织是
        if(!"".equals(orgName) &&  json.getString("orgName") != null) {
            orgName = json.getString("orgName");
        }
        sql.append("\t\tand oos.name = '"+orgName+"'\n" );
        String nyear = "";
        if("".equals(json.getString("nyear")) || json.getString("nyear") ==null){
            String[] date = new UFDateTime().toString().split("-");
            nyear = date[0];
        }else{
            nyear =  json.getString("nyear");
        }

        sql.append("\t\tand hbc.nyear in ( '"+nyear+"','"+(Integer.valueOf(nyear)+1)+"') \n" ); // 需要当前年及下一年

        if(!"".equals(json.getString("deptName")) &&  json.getString("deptName") != null) {
            sql.append("\t\tand dept.name in ('"+StringUtils.join(json.getString("deptName").split(","), "','")+"') \n" );
        }
        if(!"".equals(json.getString("szxmName")) &&  json.getString("szxmName") != null) {
            sql.append("\t\tand bis.name in ('"+StringUtils.join(json.getString("szxmName").split(","), "','")+"') \n" ); // -- 收支项目
        }
        if(!"".equals(json.getString("xmName")) &&  json.getString("xmName") != null) {
            sql.append("\t\tand pjappl.pjname in ('"+StringUtils.join(json.getString("xmName").split(","), "','")+"') \n" ); // -- 项目名称
        }
        if(!"".equals(json.getString("zjlyName")) &&  json.getString("zjlyName") != null) {
            sql.append("\t\tand bf.name in ('"+StringUtils.join(json.getString("zjlyName").split(","), "','")+"') \n"); // --- 资金来源名称
        }
        return sql.toString();
    }

    /**
     * 创建查询连接
     * @return
     */
    public BaseDAO getBaseDAO() {
        return this.baseDAO == null ? new BaseDAO() : this.baseDAO;
    }
}
